** Florida analyses 

global output ""
global data ""
cd $output


clear 


**************************************************
** Fig A2 / A3: length of stay within an agency **
**************************************************

use	  "$data/FL_officer_data_anon", clear
sort ptbid start_date


* Voluntary separations codes: see Separation_codes_FL.xlxs
gen voluntary_sep = sepreason=="Vol" | sepreason=="Ret"  | sepreason=="ML"  |  /// 
sepreason=="ELOA"  | sepreason=="VS-VAP"  | sepreason=="SEA"  | sepreason=="LOA"  | sepreason=="TWA"
gen involuntary_sep = voluntary_sep== 0 & sepreason!=""
gen leave_policing = sep_date!=. & ptbid !=ptbid[_n+1]
gen leave_other_job = sep_date!=. & ptbid ==ptbid[_n+1]
gen age_start = Y_start -birth_year 


*keep ptbid agency  start_date sep_date  voluntary_sep involuntary_sep leave_policing  leave_other_job young_work Y_start

compress

duplicates drop 

gen length_position =  sep_date - start_date
replace length_position = . if length_position<0
gen length_pos = length_position
replace length_pos = 21976 - start_date if sep_date==.



*** Figuring out what to do with people who have multiple stints in the same department 
sort ptbid agency start_date 
gen length = start_date - sep_date[_n-1] if ptbid ==ptbid[_n-1] & agency ==agency[_n-1]
summarize length, detail 
gen status_change = length !=. & length <= 180
replace sep_date = sep_date[_n+1] if status_change==1 
replace sepreason = sepreason[_n+1] if status_change==1 
drop if status_change[_n-1]==1 
drop status_change 



forvalues i = 1(1)15{
local j = `i'-1
gen Year`i' = length_pos < 365*`i'& length_pos>=365*`j'
replace Year`i' = . if start_date + 365*`i' > 21976
}


forvalues i = 1(1)15{
local j = `i'-1
gen vol_Year`i' = length_pos < 365*`i'& length_pos>=365*`j' & voluntary_sep == 1
replace vol_Year`i' = . if start_date + 365*`i' > 21976
}

forvalues i = 1(1)15{
local j = `i'-1
gen invol_Year`i' = length_pos < 365*`i'& length_pos>=365*`j' & involuntary_sep == 1
replace invol_Year`i' = . if start_date + 365*`i' > 21976
}

forvalues i = 1(1)15{
local j = `i'-1
gen Quit_Year`i' = length_pos < 365*`i'& length_pos>=365*`j' & leave_policing == 1 & voluntary_sep == 1
replace Quit_Year`i' = . if start_date + 365*`i' > 21976
}


forvalues i = 1(1)15{
local j = `i'-1
gen Change_Year`i' = length_pos < 365*`i'& length_pos>=365*`j' & leave_other_job == 1 & voluntary_sep == 1
replace Change_Year`i' = . if start_date + 365*`i' > 21976
}
 
 
 


collapse (mean) Year* vol_Year* invol_Year* Quit_Year* Change_Year*
gen i = _n
reshape long Year vol_Year invol_Year other_Year Quit_Year Change_Year, i(i) j(years)

twoway (line Change_Year years)  (line Quit_Year years) , ///
ytitle(Fraction separating) xtitle(Years at agency)  plotregion(margin(zero)) yscale(range(0 0.15)) /// 
 ylabel(0(0.05)0.15) ymtick(0(0.15)0.05) legend(label(1 "Move to new FL agency") label(2 "Cease FL policing"))
graph export "/Users/aouss/Desktop/Research/PoliceMobility/FlJan2023/FigA2_Yearly_quit_change_FL.png", replace




twoway (line Year years)  (line vol_Year years)  (line invol_Year years), ///
ytitle(Fraction separating) xtitle(Years at agency)  plotregion(margin(zero)) yscale(range(0 0.15)) /// 
 ylabel(0(0.05)0.2) ymtick(0(0.2)0.05) ///
   legend(label(1 "Separations (total)") label(2 "Voluntary separations") label(3 "Involuntary separations"))
graph export "/Users/aouss/Desktop/Research/PoliceMobility/FlJan2023/FigA1_Yearly_separations_motive_FL.png", replace

**********************************************************
** Tab A2: in the first 5 years: who stays, who leaves? **
**********************************************************

use "$data/FL_officer_data_anon", clear 
sort ptbid start_date

** Keep officers whom we observe for at least 5Y. 
keep if 21976 - start_date > 365*5
replace sep_date = . if sep_date - start_date > 365*5

gen voluntary_sep = sepreason=="Vol" | sepreason=="Ret"  | sepreason=="ML"  |  /// 
sepreason=="ELOA"  | sepreason=="VS-VAP"  | sepreason=="SEA"  | sepreason=="LOA"  | sepreason=="TWA"
gen involuntary_sep = voluntary_sep== 0 & sepreason!=""
gen leave_policing = sep_date!=. & ptbid !=ptbid[_n+1]  & sep_date !=.
gen leave_other_job = sep_date!=. & ptbid ==ptbid[_n+1]  & sep_date !=.
gen still_work =   sep_date ==.

gen age_start = Y_start - birth_year
replace age_start = . if age_start<21
replace age_start = . if age_start>60


replace race_code = lower(race_code )

keep if Y_start >=2010 & Y_start <=2015


keep ptbid voluntary_sep involuntary_sep leave_policing leave_other_job still_work sex race  age_start educ

gen female = sex == "F"
gen Black = race == "blk" | race == "b"
gen White = race == "wh" | race == "wht" | race == "w"
gen Hispanic = race == "his" | race == "h"
gen Other_race = Black == 0 & White == 0 & Hispanic == 0
label var Other_race "Other race"

gen assoc = educ=="A"
label var assoc "Associates Degree"
gen BA = educ =="B" | educ == "D"
label var BA "Bachelors Degree or more"
gen HS = educ =="H" | educ =="M"
label var HS "High School or less"
gen Unknown = educ ==""
label var Unknown "Unknown education"

label var voluntary_sep  "Left" 
label var involuntary_sep  "Fired" 
label var still_work  "Still working" 
label var leave_policing  "Left FL" 
label var leave_other_job  "New job in FL" 

estpost sum still_work voluntary_sep involuntary_sep leave_policing leave_other_job  
est store all
estpost sum still_work voluntary_sep involuntary_sep leave_policing leave_other_job  if female==0
est store male
estpost sum still_work voluntary_sep involuntary_sep leave_policing leave_other_job  if female==1
est store fem
estpost sum   still_work voluntary_sep involuntary_sep leave_policing leave_other_job  if  Black  ==1
est store bl
estpost sum  still_work  voluntary_sep involuntary_sep leave_policing leave_other_job  if White ==1
est store wh
estpost sum  still_work  voluntary_sep involuntary_sep leave_policing leave_other_job  if Hispanic ==1
est store hisp

estpost sum  still_work  voluntary_sep involuntary_sep leave_policing leave_other_job  if assoc==1
est store asso
estpost sum   still_work voluntary_sep involuntary_sep leave_policing leave_other_job  if BA==1
est store BA
estpost sum   still_work voluntary_sep involuntary_sep leave_policing leave_other_job  if HS==1
est store HS
estpost sum   still_work voluntary_sep involuntary_sep leave_policing leave_other_job  if Unknown==1
est store un



esttab all male fem wh bl hisp HS asso BA using "/Users/aouss/Desktop/Research/PoliceMobility/FlJan2023/sociodemog_Fl.tex", replace ///
mtitles("All" "Male" "Female" "White" "Black" "Hispanic"  "HS"  "Assoc" "BA") ///
cells("mean(fmt(2))") label nonumber nodepvars  title("Sociodemographics and employment status, 5 years after start date at an agency, in Florida" \label{sociodemog})        


esttab all male fem wh bl hisp HS asso BA using "/Users/aouss/Desktop/Research/PoliceMobility/FlJan2023/sociodemog_Fl.rtf", replace ///
mtitles("All" "Male" "Female" "White" "Black" "Hispanic"  "HS"  "Assoc" "BA") ///
cells("mean(fmt(2))") label nonumber nodepvars  title("Tab A2: Sociodemographics and employment status, 5 years after start date at an agency, in Florida" \label{sociodemog})        


*********************************************
** Tab A1: Number of jobs people worked at **
********************************************* 

use "$data/FL_officer_data_anon", clear 

keep ptbid agency
duplicates drop 
duplicates tag ptbid, gen(nb_agencies)

label var nb_agencies "Number of agencies"
replace nb_agencies = nb_agencies + 1
replace nb_agencies = 4 if nb_agencies >4
tostring nb_agencies, replace 
replace nb_agencies = "4+" if nb_agencies=="4"
estpost tab nb_agencies
	est store a
esttab a using "/Users/aouss/Desktop/Research/PoliceMobility/FlJan2023/nb_agencies_FT_FL.tex", nonumbers cells(pct(fmt(1))) sfmt(fmt(%12.2f))  /// 
replace label title("Percentage of officers that worked in 1, 2, 3 or 4+ agencies in Florida" \label{nbjobFT}) 

esttab a using "/Users/aouss/Desktop/Research/PoliceMobility/FlJan2023/nb_agencies_FT_FL.rtf", nonumbers cells(pct(fmt(1))) sfmt(fmt(%12.2f))  /// 
replace label title("Tab A1: Percentage of officers that worked in 1, 2, 3 or 4+ agencies in Florida") 
   
***************************************
** Fig A5: Size of agency & turnover **
***************************************

use "$data/FL_officer_data_anon", clear 
 keep if EmployeesNumber !=. 

forvalues i = 2000(1)2021{
gen working_`i' =Y_start <= `i' & (Y_stop >= `i' | Y_stop == .)
gen quit_`i' = Y_stop == `i'
by agency, sort: egen nb_active`i' = sum(working_`i') 
by agency, sort: egen nb_quit`i' = sum(quit_`i') 
}

keep agency nb_active* nb_quit* EmployeesNumber  
 duplicates drop 
 

  
reshape long nb_active nb_quit, i(agency) j(year)
gen quit_rate = nb_quit/EmployeesNumber

 xtile size = EmployeesNumber  , nq(10)
 by size , sort: egen m = mean(EmployeesNumber ) 
 label var m "Number of employees"
 by size , sort: egen q = mean(quit_rate) 
 label var q "Separation rate"

 twoway (line q size if year == 2018,  c(l) yaxis(1)) /// 
 (bar m size if year == 2018, c(l) yaxis(2) fcolor(none) lcolor(black%85)  yscale(range(0 100) axis(2))),  xtitle(Decile of agency size) /// 
 legend(ring(1) position(6)  bplacement(south))  plotregion(margin(zero)) xlabel(1(1)10, notick)
 graph export "/Users/aouss/Desktop/Research/PoliceMobility/FlJan2023/FigA4_Agency_size_quit_2018_FL.png", replace

********************************* 
** Fig A4: Turnover and salary **
********************************* 

use "$data/FL_officer_data_anon", clear 


 keep if EmployeesNumber !=. 

gen vol_leave =  sepreason=="Vol" | sepreason=="Ret"  | sepreason=="ML"  |  /// 
sepreason=="ELOA"  | sepreason=="VS-VAP"  | sepreason=="SEA"  | sepreason=="LOA"  | sepreason=="TWA"

 
gen MedianSalary = payroll/EmployeesNumber*12
 
sort ptbid start_date

gen sal_change = MedianSalary[_n+1] - MedianSalary if ptbid ==ptbid[_n+1] & vol_leave==1
replace sal_change = . if  ptbid ==ptbid[_n+1] & agency==agency[_n+1]

sum sal_change , detail
local med= round(r(p50)) 

sum MedianSalary if ptbid ==ptbid[_n+1]  & agency!=agency[_n+1] & vol_leave==1, detail
local leave= round(r(p50)) 

sum MedianSalary if ptbid !=ptbid[_n+1]  | (ptbid ==ptbid[_n+1] & agency==agency[_n+1]), detail
local stay= round(r(p50)) 



gen leave = ptbid == ptbid[_n+1] & agency!=agency[_n+1]
gen higher = leave == 1 & MedianSalary[_n+1] > MedianSalary

sum higher if leave == 1 & vol_leave==1
local high=   round(100*r(mean))  


hist sal_change  , frac note("Median salary if stay=`stay';  Median salary if leave=`leave'; Median salary increase if leave=`med'." /// 
"If they move, `high'% go to a higher-paying agency." ) xtitle(Distribution of median salary changes) ytitle("Fraction") /// 
 plotregion(margin(zero))  xlabel(, format( %15.0fc) labsize(vsmall))
  graph export "/Users/aouss/Desktop/Research/PoliceMobility/FlJan2023/FigA3_salary_turnover_FL.png", replace


  

